﻿namespace Access.Duoc
{
    using ConfigDatabase.Class;
    using Npgsql;
    using Access;
    using BaseClass.Chung;
    using BaseClass.Duoc;
    using System;
    using System.Data;

    public class DPhieuDatHang
    {
        private ConnectDatabase _condata;
        private NpgsqlConnection _conn;
        private General _xuli;

        public DPhieuDatHang()
        {
            this._condata = new ConnectDatabase();
            this._xuli = new General(this._condata);
        }

        public DPhieuDatHang(ConnectDatabase condata)
        {
            this._condata = condata;
            this._xuli = new General(this._condata);
        }

        public long f_get_capsophieu()
        {
            long num = 0L;
            try
            {
                num = long.Parse(this._xuli.f_Get_dulieu("select max(" + dtoDPhieuDatHang.columnSOPHIEU + ") as sophieu  from " + this._xuli.pSchema + "." + dtoDPhieuDatHang.tableName + " where to_char(" + dtoDPhieuDatHang.columnNGAY + ",'yyyy')='" + DateTime.Now.ToString("yyyy") + "'").Tables[0].Rows[0][0].ToString());
            }
            catch
            {
            }
            return (num + 1L);
        }

        private string f_getsql_phieudathang(long id, string tungay, string denngay, int trangthai)
        {
            string str = this._condata.Schema + ".";
            return ("select a." + dtoDPhieuDatHang.columnID + " as id,a." + dtoDPhieuDatHang.columnSOPHIEU + " as sophieu,to_char(" + dtoDPhieuDatHang.columnNGAY + ",'dd/mm/yyyy hh24:mi') as ngay,to_char(" + dtoDPhieuDatHang.columnNGAYGIAO + ",'dd/mm/yyyy hh24:mi') as ngaygiao,a." + dtoDPhieuDatHang.columnIDDANGNHAP + " as iddangnhap,a." + dtoDPhieuDatHang.columnIDNHACUNGCAP + " as idnhacc,b2." + dtoDDMNhaCungCap.columnTEN + " as tennhacc,b2." + dtoDDMNhaCungCap.columnDIACHI + " as diachi,b2." + dtoDDMNhaCungCap.columnDIENTHOAI + " as dienthoai,b2." + dtoDDMNhaCungCap.columnDAIDIEN + " as nguoidaidien,b." + dtoDMDangNhap.columnTENNGUOIDUNG + " as tennguoidangnhap,a." + dtoDPhieuDatHang.columnGHICHU + " as ghichu,a." + dtoDPhieuDatHang.columnPHUONGTHUCTHANHTOAN + " as ptthanhtoan,a." + dtoDPhieuDatHang.columnPHUONGTHUCGIAOHANG + " as ptgiaohang from " + str + dtoDPhieuDatHang.tableName + " a left join " + str + dtoDMDangNhap.tableName + " b on a." + dtoDPhieuDatHang.columnIDDANGNHAP + "=b." + dtoDMDangNhap.columnID + " left join " + str + dtoDDMNhaCungCap.tableName + " b2 on a." + dtoDPhieuDatHang.columnIDNHACUNGCAP + "=b2." + dtoDDMNhaCungCap.columnID + " inner join (select distinct idphieu from (" + this.f_getsql_phieudathangchitiet(id, trangthai) + ") a) a2 on a2.idphieu=a." + dtoDPhieuDatHang.columnID + " where 1=1 " + ((id == 0L) ? "" : string.Concat(new object[] { " and a.", dtoDPhieuDatHang.columnID, "=", id })) + ((tungay == "") ? "" : (" and to_timestamp(to_char(a." + dtoDPhieuDatHang.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')>=to_timestamp('" + tungay + "','dd/mm/yyyy')")) + ((denngay == "") ? "" : (" and to_timestamp(to_char(a." + dtoDPhieuDatHang.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')<=to_timestamp('" + denngay + "','dd/mm/yyyy')")) + " order by a." + dtoDPhieuDatHang.columnNGAY + " desc");
        }

        private string f_getsql_phieudathangchitiet(long idphieudathang, int trangthai)
        {
            string str5;
            string str = "";
            if (idphieudathang != 0L)
            {
                object obj2 = str;
                str = string.Concat(new object[] { obj2, " and b.", dtoDPhieuDatHangCT.columnIDPHIEU, "=", idphieudathang });
            }
            if (trangthai == 1)
            {
                str5 = str;
                str = str5 + " and b." + dtoDPhieuDatHangCT.columnSOLUONGDAGIAO + "<b." + dtoDPhieuDatHangCT.columnSOLUONG;
            }
            else if (trangthai == 2)
            {
                str5 = str;
                str = str5 + " and b." + dtoDPhieuDatHangCT.columnSOLUONGDAGIAO + "=b." + dtoDPhieuDatHangCT.columnSOLUONG;
            }
            string str2 = this._condata.Schema + ".";
            return ("select b." + dtoDPhieuDatHangCT.columnIDPHIEU + " as idphieu,b." + dtoDPhieuDatHangCT.columnID + " as id,b2." + dtoDDMDuocPham.columnID + " as iddp,b2." + dtoDDMDuocPham.columnTEN + " as tendp,b2." + dtoDDMDuocPham.columnMA + " as madp,b2." + dtoDDMDuocPham.columnDVT + " as dvt,b2." + dtoDDMDuocPham.columnMAVACH + " as mavach,b." + dtoDPhieuDatHangCT.columnSOLUONGTONG + " as soluongtong,b." + dtoDPhieuDatHangCT.columnSOLUONG + " as soluong,b." + dtoDPhieuDatHangCT.columnIDQUYDOI + " as idquydoi,b." + dtoDPhieuDatHangCT.columnDONGIA + " as dongia,b." + dtoDPhieuDatHangCT.columnSOLUONG + "*b." + dtoDPhieuDatHangCT.columnDONGIA + " as thanhtien,b." + dtoDPhieuDatHangCT.columnSOLUONGDAGIAO + " as soluonggiao,b." + dtoDPhieuDatHangCT.columnIDDANGNHAP + " as iddangnhap,b." + dtoDPhieuDatHangCT.columnHANGSX + " as hangsx,case when b3." + dtoDDMQuyDoi.columnID + " is null then b2." + dtoDDMDuocPham.columnDVT + " else b3." + dtoDDMQuyDoi.columnTEN + " end as tenquydoi from " + str2 + dtoDPhieuDatHang.tableName + " a inner join " + str2 + dtoDPhieuDatHangCT.tableName + " b on a." + dtoDPhieuDatHang.columnID + "=b." + dtoDPhieuDatHangCT.columnIDPHIEU + " inner join " + str2 + dtoDDMDuocPham.tableName + " b2 on b." + dtoDPhieuDatHangCT.columnIDDUOCPHAM + "=b2." + dtoDDMDuocPham.columnID + " left join " + str2 + dtoDDMQuyDoi.tableName + " b3 on b." + dtoDPhieuDatHangCT.columnIDQUYDOI + "=b3." + dtoDDMQuyDoi.columnID + " where " + ((str != "") ? (" 1=1" + str) : " 1=0") + " order by a." + dtoDPhieuDatHang.columnNGAY + " desc");
        }

        public bool f_huyphieunhap(long id)
        {
            int num = -1;
            try
            {
                NpgsqlConnection connect = new NpgsqlConnection(this._condata.StringConnect);
                connect.Open();
                NpgsqlTransaction transaction = connect.BeginTransaction();
                try
                {
                    num = this._xuli.f_Update_dulieu(string.Concat(new object[] { "delete from ", this._xuli.pSchema, ".", dtoDPhieuDatHangCT.tableName, " where ", dtoDPhieuDatHangCT.columnIDPHIEU, "=", id }), connect);
                    num = -1;
                    num = this._xuli.f_Update_dulieu(string.Concat(new object[] { "delete from ", this._xuli.pSchema, ".", dtoDPhieuDatHang.tableName, " where ", dtoDPhieuDatHang.columnID, "=", id }), connect);
                    if (num > -1)
                    {
                        transaction.Commit();
                    }
                    else
                    {
                        transaction.Rollback();
                    }
                }
                catch
                {
                    transaction.Rollback();
                }
                finally
                {
                    connect.Close();
                    connect.Dispose();
                }
            }
            catch
            {
            }
            return (num > -1);
        }

        public long f_insert_phieudathang(dtoDPhieuDatHang dathang, dtoDPhieuDatHangCT[] chitiet)
        {
            long num2;
            this._conn = new NpgsqlConnection(this._condata.StringConnect);
            this._conn.Open();
            try
            {
                dathang.pID = long.Parse(this._xuli.f_Get_CapID());
                if (!dathang.Insert(this._conn))
                {
                    return 0L;
                }
                NpgsqlTransaction transaction = this._conn.BeginTransaction();
                int index = 0;
                index = 0;
                while (index < chitiet.Length)
                {
                    chitiet[index].pIDPHIEU = dathang.pID;
                    if (chitiet[index].pID != 0L)
                    {
                        if (!chitiet[index].Update(this._conn))
                        {
                            transaction.Rollback();
                            break;
                        }
                    }
                    else
                    {
                        chitiet[index].pID = long.Parse(this._xuli.f_Get_CapID());
                        if (!chitiet[index].Insert(this._conn))
                        {
                            chitiet[index].pID = 0L;
                            transaction.Rollback();
                            break;
                        }
                    }
                    index++;
                }
                if (index == chitiet.Length)
                {
                    transaction.Commit();
                    return dathang.pID;
                }
                dathang.Delete();
                num2 = 0L;
            }
            catch
            {
                num2 = 0L;
            }
            finally
            {
                this._conn.Close();
                this._conn = null;
            }
            return num2;
        }

        public DataTable f_loaddata_dathang(long id, string tungay, string denngay)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = this.f_getsql_phieudathang(id, tungay, denngay, 0);
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public DataTable f_loaddata_dathang_chuagiaohang(long id, string tungay, string denngay)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = this.f_getsql_phieudathang(id, tungay, denngay, 1);
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public DataTable f_loaddata_dathang_dagiaohang(long id, string tungay, string denngay)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = this.f_getsql_phieudathang(id, tungay, denngay, 2);
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public DataTable f_loaddata_dathangct(long idphieudathang)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = this.f_getsql_phieudathangchitiet(idphieudathang, 0);
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public long f_update_dathang(dtoDPhieuDatHang dathang, dtoDPhieuDatHangCT[] chitiet)
        {
            long num2;
            this._conn = new NpgsqlConnection(this._condata.StringConnect);
            this._conn.Open();
            NpgsqlTransaction transaction = this._conn.BeginTransaction();
            try
            {
                if (!dathang.Update(this._conn))
                {
                    transaction.Rollback();
                    return 0L;
                }
                string str = "";
                int index = 0;
                index = 0;
                while (index < chitiet.Length)
                {
                    if (chitiet[index].pID != 0L)
                    {
                        if (!chitiet[index].Update(this._conn))
                        {
                            transaction.Rollback();
                            break;
                        }
                    }
                    else
                    {
                        chitiet[index].pID = long.Parse(this._xuli.f_Get_CapID());
                        if (!chitiet[index].Insert(this._conn))
                        {
                            transaction.Rollback();
                            break;
                        }
                    }
                    str = str + chitiet[index].pID + ",";
                    index++;
                }
                if (index == chitiet.Length)
                {
                    transaction.Commit();
                    this._xuli.f_Update_dulieu(string.Concat(new object[] { "delete from ", this._xuli.pSchema, ".", dtoDPhieuDatHangCT.tableName, " where ", dtoDPhieuDatHangCT.columnID, " not in(", str.Trim(new char[] { ',' }), ")  and ", dtoDPhieuDatHangCT.columnSOLUONGDAGIAO, "=0 and ", dtoDPhieuDatHangCT.columnIDPHIEU, "=", dathang.pID }));
                    return dathang.pID;
                }
                transaction.Rollback();
                num2 = 0L;
            }
            catch
            {
                transaction.Rollback();
                num2 = 0L;
            }
            finally
            {
                this._conn.Close();
                this._conn = null;
            }
            return num2;
        }
    }
}

